import sqlalchemy
import pymysql
import numpy as np
import pandas as pd
import os

class DataOperator:

    def __init__(self):
        # self.cp = ConfigParser.ConfigParser()
        # self.cp.read(os.path.split(os.path.realpath(__file__))[0] + '/Config')
        # self.host = '127.0.0.1'
        # self.user = 'root'
        # self.password ='root'
        # self.db = 'pc'
        # self.charset = 'utf8'
        # self.host = '192.168.9.55'
        # self.port = 3306
        # self.host = '124.207.24.170'
        # self.port = 53306
        # self.user = 'aiops_demo'
        # self.password = 'aiops2022'
        # self.db = 'aiops_demo'
        # self.charset = 'utf8'
        self.host = os.getenv('MYSQL_HOST')
        self.port = int(os.getenv('MYSQL_PORT'))
        self.user = os.getenv('MYSQL_USER')
        self.password = os.getenv('MYSQL_PASSWORD')
        self.db = os.getenv('MYSQL_DB')
        self.charset = 'utf8'
		

    def insert(self,img):
        # 创建连接
        conn = pymysql.connect(host= self.host,port=self.port,
                               user= self.user,password = self.password,
                               db= self.db,charset=self.charset)
        cursor = conn.cursor()

        # 注意使用Binary()函数来指定存储的是二进制
        # cursor.execute("INSERT INTO demo_pic_repo SET touxiang_data= %s" % pymysql.Binary(img))
        # sql = "INSERT INTO pc_graphmatrix(data) VALUES  (%s)"
        sql = "INSERT INTO pc_graphmatrix(id,data) VALUES  (1,%s) ON DUPLICATE KEY UPDATE id=1"
        cursor.execute(sql, img)

        # 提交，不然无法保存新建或者修改的数据
        conn.commit()
        cursor.close()
        conn.close()

    def update(self,img):
        # 创建连接
        conn = pymysql.connect(host=self.host,port=self.port,
                               user=self.user, password=self.password,
                               db=self.db, charset=self.charset)
        cursor = conn.cursor()

        # 注意使用Binary()函数来指定存储的是二进制
        # cursor.execute("INSERT INTO demo_pic_repo SET touxiang_data= %s" % pymysql.Binary(img))
        sql = "update pc_graphmatrix set data = (%s) where id = 1"
        cursor.execute(sql, img)

        # 提交，不然无法保存新建或者修改的数据
        conn.commit()
        cursor.close()
        conn.close()

    def getGraph(self):
        conn = pymysql.connect(host=self.host,port=self.port,
                               user=self.user, password=self.password,
                               db=self.db, charset=self.charset)
        cursor = conn.cursor()
        sql = 'select data from pc_graphmatrix where id=1'
        cursor.execute(sql)
        result = cursor.fetchone()
        #     results = cursor.fetchall() #用于返回多条数据
        print(result[0])
        print(type(result[0]))
        cursor.close()
        conn.close()
        graph = np.frombuffer(result[0], dtype=np.float64)
        graph = graph.reshape(5, 5)
        return graph

    def getData(self):
        conn = pymysql.connect(host=self.host,port=self.port,
                               user=self.user, password=self.password,
                               db=self.db, charset=self.charset)
        sql = 'select * from tb_log_times'
        data = pd.read_sql(sql, conn)
        conn.close()
        return data